package com.huaxia.dao.performance;

import com.huaxia.pojo.performance.SzLongPerformance;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/04/26 14:36
 */

public interface SzLongPerformanceMapper {

    @Select("select sb.saledeptsname,nvl(a.dayAdvance,0)saledeptDayAdvance,nvl(b.monthAdvance,0)saledeptMonthAdvance,(nvl(c.dayInSure,0)-nvl(d.dayYt,0))saledeptDayInSure,\n" +
            "            (nvl(e.monthInSure,0)-nvl(f.monthYt,0))saledeptMonthInSure from SD_SZ sb left join(\n" +
            "            select saledeptCODE, SUM(PREPREM)dayAdvance from FACT_YX_PREPREM_LIST_day where TO_CHAR(RECEIVE_DATE,'YYYY/MM/DD')=\n" +
            "    TO_CHAR(SYSDATE,'YYYY/MM/DD') and (IS_IMMEDIATE_TRANSFER = 'Y' or IS_IMMEDIATE_TRANSFER is null) and (APPLY_STATUS_CODE NOT IN('2040','4010','10') or APPLY_STATUS_CODE IS NULL) and longproduct_flag = 'Y' GROUP BY saledeptCODE\n" +
            "                                                    )a on sb.saledeptcode=a.saledeptcode left join(\n" +
            "            select saledeptCODE, SUM(PREPREM)monthAdvance from FACT_YX_PREPREM_LIST_day where TO_CHAR(RECEIVE_DATE,'YYYY/MM')=\n" +
            "    TO_CHAR(SYSDATE,'YYYY/MM') and (IS_IMMEDIATE_TRANSFER = 'Y' or IS_IMMEDIATE_TRANSFER is null) and (APPLY_STATUS_CODE NOT IN('2040','4010','10') or APPLY_STATUS_CODE IS NULL) and longproduct_flag = 'Y' \n" +
            "    GROUP BY saledeptCODE \n" +
            "                   )b on sb.saledeptcode=b.saledeptcode left join(\n" +
            "            select saledeptCODE, SUM(WRITTENPREM)dayInSure from FACT_YX_PREPREM_LIST_day where TO_CHAR(ISSUE_DATE,'YYYY/MM/DD')=\n" +
            "    TO_CHAR(SYSDATE,'YYYY/MM/DD') and longproduct_flag = 'Y' GROUP BY saledeptCODE\n" +
            "                                                    )c on sb.saledeptcode=c.saledeptcode left join(\n" +
            "            select saledeptCODE, SUM(WRITTENPREM)dayYt from FACT_YX_PREPREM_LIST_day where TO_CHAR(YT_DATE,'YYYY/MM/DD')\n" +
            "            = TO_CHAR(SYSDATE,'YYYY/MM/DD') and longproduct_flag = 'Y' GROUP BY saledeptCODE\n" +
            "                                                    )d on sb.saledeptcode=d.saledeptcode left join(\n" +
            "            select saledeptCODE, SUM(WRITTENPREM)monthInSure from FACT_YX_PREPREM_LIST_day where TO_CHAR(ISSUE_DATE,'YYYY/MM')\n" +
            "            =TO_CHAR(SYSDATE,'YYYY/MM') and longproduct_flag = 'Y' GROUP BY saledeptCODE\n" +
            "                  )e on sb.saledeptcode=e.saledeptcode left join(\n" +
            "            select saledeptCODE, SUM(WRITTENPREM)monthYt from FACT_YX_PREPREM_LIST_day where TO_CHAR(YT_DATE,'YYYY/MM')\n" +
            "            =TO_CHAR(SYSDATE,'YYYY/MM') and longproduct_flag = 'Y'  GROUP BY saledeptCODE\n" +
            "                                )f on sb.saledeptcode=f.saledeptcode order by saledeptMonthInSure desc")
    List<SzLongPerformance> getSzLongPerformanceThisMon();

}

